﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.SIESystemManager.Calculate
{
    public class GetOperation_Data
    {
        /// <summary>
        /// 动作定义
        /// </summary>
        /// <returns></returns>
        public DataTable GetOperationSql(string Effective_time)
        {
            string sql = "";
            sql = @"SELECT 
       [WORKSTATION_LIST]
      ,[OPERATIONAL_NAME]
      ,[TYPE]
      ,[UNIT]
      ,[UNIT_WORKHOURS]
      ,[ATTACH_WORKHOURS]
      ,[DESCRIPTION]
  FROM [LamexMESManager].[dbo].[B_IE_Operational_Definition]
where  EFFECTIVE_TS<='" + Effective_time + " 23:59:59' and END_TS>='" + Effective_time + " 00:00:00' "; //增加生效时间监控";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 宽度系数
        /// </summary>
        /// <returns></returns>
//        public DataTable GetFactor(string Effective_time)
//        {
//            string sql = @"SELECT distinct 
//        [NUMBER]
//      , left(ALLOWANCE_FACTORS,len(ALLOWANCE_FACTORS)-1) as ALLOWANCE_FACTORS
//  FROM [LamexMESManager].[dbo].[B_IE_Allowance_Factor]
//where  EFFECTIVE_TS<='" + Effective_time + " 23:59:59' and END_TS>='" + Effective_time + " 00:00:00' "; //增加生效时间监控
//            DataTable dt = new DataTable();
//            dt = BaseBuildSql.GetDataTableBySql(sql);
//            return dt;
//        }
        public DataTable GetFactor(string Effective_time)
        {
            string sql = @"SELECT  [PROCESS_CODE]
      ,[ALLOWANCE_FACTOR]
      ,[EFFECTIVE_DATE]
      ,[EXPIRY_DATE]   
  FROM [LamexMESManager].[dbo].[IE_WorkCenter_AllowanceFactor]
  where  EFFECTIVE_DATE<='" + Effective_time + " 23:59:59' and EXPIRY_DATE>='" + Effective_time + " 00:00:00' "; //增加生效时间监控"
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

        /// <summary>
        /// 获取BIMA PH 机器数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetMachineData(string verNo)
        {
            string sql = @"SELECT [Machine_Name]
      ,[Part_No]
      ,[Drawing_No]
      ,[Program_Name]
      ,[Process_Time]
      ,[Ver_No]
      ,[Date_TS]
  FROM [LamexMESManager].[dbo].[B_IE_Machine_Time] where Ver_No='" + verNo + "'";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetRouting()
        {
            string sql = @"SELECT 
         IDNO
       ,[Routing_Group]
      ,[Calculate]
      ,[Calculate_Desc]
  FROM [LamexMESManager].[dbo].[B_IE_Calculator_Routing] where 1=1 and Routing_Group like '44%' ";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetRouting_Temp()
        {
            string sql = @"SELECT 
         IDNO
       ,[Routing_Group]
      ,[Calculate]
      ,[Calculate_Desc]
  FROM [LamexMESManager].[dbo].[B_IE_Calculator_Routing] where 1=1  ";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 获取标准BOM主数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetBOM_MASTER(string Version)
        {
            string sql = @"SELECT 
       [MATERIAL_NO]
      ,[DESCRIBES]
      ,[DRAWING_NO]
      ,[MRP]
      ,[CUTTING_SIZE]
      ,[PRODUCT_DI]
      ,[SIZE]
      ,[IMPORT_TS]
      ,[MATERRIAL_GROUP]
      ,[PURCHASE_TYPE]
      ,[PURCHASE_SPECICAL]
      ,[CALCULATE_FLAG]
      ,[UPLOAD_TS]
      ,[Version]
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_MASTER_DATA_STD] WHERE 
ISNULL(Version,'')='" + Version + "' and MRP IN ('W00','MW0') AND ISNULL(CALCULATE_FLAG,'')='' ";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 获取SJBOM主数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetBOM_MASTERSJ()
        {
            string sql = @"SELECT 
       [MATERIAL_NO]
      ,[DESCRIBES]
      ,[DRAWING_NO]
      ,[MRP]
      ,[CUTTING_SIZE]
      ,[PRODUCT_DI]
      ,[SIZE]
      ,[IMPORT_TS]
      ,[MATERRIAL_GROUP]
      ,[PURCHASE_TYPE]
      ,[PURCHASE_SPECICAL]
      ,[CALCULATE_FLAG]
      ,[UPLOAD_TS]
      ,[Version]
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_MASTER_DATA_SJR] WHERE 
  MRP='W00' AND ISNULL(CALCULATE_FLAG,'')='' ";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

        /// <summary>
        /// 获取BOM数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetBOM(string Version)
        {
            string sql = @"SELECT
       [MATERIAL_NO]
      ,[COMPONENT]
      ,[DESCRIBES]
      ,[COMPONENT_QTY]
      ,[UNIT]
      ,[SINGLE_QTY]
      ,[DRAWING_NO]
      ,[CUTTING_SIZE]
      ,[Width]
      ,[Length]
      ,[Thiness]
      ,[MATERIAL_GROUP]
      ,[EDGE_CODE]
      ,[IMPORT_TS]
      ,[Version]
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_BOM_DATA_STD] where ISNULL(Version,'')='" + Version + "' ";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 获取BOM数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetBOMSJ()
        {
            string sql = @"SELECT
       [MATERIAL_NO]
      ,[COMPONENT]
      ,[DESCRIBES]
      ,[COMPONENT_QTY]
      ,[UNIT]
      ,[SINGLE_QTY]
      ,[DRAWING_NO]
      ,[CUTTING_SIZE]
      ,[Width]
      ,[Length]
      ,[Thiness]
      ,[MATERIAL_GROUP]
      ,[EDGE_CODE]
      ,[IMPORT_TS]
      ,[Version]
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_BOM_DATA_SJR]  ";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 获取BOM特征适用表
        /// </summary>
        /// <returns></returns>
        public DataTable GetBOM_NECESSARY()
        {
            string sql = @"SELECT 
       [Operation_Name]
      ,[Material_No]
      ,[Describe]
      ,[Create_TS]
      ,[Create_User]
      ,[Status]
  FROM [LamexMESManager].[dbo].[B_IE_COMPONENT_NECESSARY]";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 获取特征表字段
        /// </summary>
        public DataTable GetOpertion_Data()
        {
            string sql = @"SELECT
            A.name AS table_name,
            SUBSTRING(B.name,0,4) AS column_name,
            C.value AS column_description
            FROM LamexMESManager.sys.tables A
            INNER JOIN LamexMESManager.sys.columns B ON B.object_id = A.object_id
            LEFT JOIN LamexMESManager. sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
            WHERE A.name = 'B_IE_Operation_Data' and LEN(b.name)>5";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 获取工艺路线简码
        /// </summary>
        /// <returns></returns>
        public DataTable GetRouting_JM()
        {
            string sql = @"SELECT 
       [GROUP_CODE]
      ,[GROUP_ROUTING]
      ,[GROUP_DEC]
  FROM [LamexMESManager].[dbo].[B_IE_ROUTING_GROUP_JM]";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetIEWorkTime_BOM()
        {
            string sql = @" SELECT PartNumber FROM [LamexMESManager].[dbo].[B_IE_WorkTimeHistory]
  where Flag='3'";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetIEWorkTime_SJ()
        {
            string sql = @" SELECT PartNumber FROM [LamexMESManager].[dbo].[B_IE_WorkTimeHistory]
  where Flag='2'";
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetSJR_DATA(string Part_No)
        {
            string sql = @" SELECT [PartNo],[Drawing_No] FROM [LamexMESManager].[dbo].[B_IE_SJR_DATA] WHERE 1=1";
            sql = sql + Part_No;
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
    }
}
